/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBLayer;

import ModelLayer.Product;
import ModelLayer.SalesLine;
import ModelLayer.SalesOrder;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Henrik-iMac
 */
public class DBSalesLine implements IFDBSalesLine{
    private Connection con;

    public DBSalesLine()
    {
        con = DbConnection.getInstance().getDBcon();
    }

    public ArrayList<SalesLine> getAllSalesLine(String wClause, boolean retrieveAssociation){
        ArrayList salesList = new ArrayList();
        wClause = "salesorderId = " + wClause;
        salesList = miscWhere(wClause, retrieveAssociation);
        return salesList;
    }



    public int insertSalesLine(SalesLine s){
        int nextId = GetMax.getMaxId("SELECT max(id) from SalesLine");
        nextId = nextId + 1;
        System.out.println("next salesline id: " + nextId + " ");

        int rc = -1;

        String query = "INSERT INTO SalesLine(id, amount, price, totalPrice, salesOrderId, productId) VALUES("+
                nextId +", " +
                s.getAmount() + ", " +
                s.getPrice() + ", " +
                s.getTotalPrice() + ", " +
                s.getSalesOrderId().getId() + ", " +
                s.getProductId().getPartNo() +")";
        System.out.println("insert: "+query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Insert exception in salesline db: "+e);
        }
        return rc;
    }
    public int deleteSalesLine(int id){
        int rc = -1;
        String query = "DELETE FROM SalesLine WHERE id = "+id;
        System.out.println(query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Delete exception in salesline db: "+e);
        }
        return rc;
    }

    private ArrayList<SalesLine> miscWhere(String wClause, boolean retrieveAssociation){
        ResultSet results;
        ArrayList<SalesLine> list = new ArrayList<SalesLine>();

        String query = buildQuery(wClause);

        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while(results.next()){
                SalesLine salesLineObj = new SalesLine();
                salesLineObj = buildSalesLine(results);
                list.add(salesLineObj);
            }
            stmt.close();

            if(retrieveAssociation){
                for(SalesLine s : list){
                    IFDBSalesOrder dbSalesOrderObj = new DBSalesOrder();
                    SalesOrder salesOrderObj = dbSalesOrderObj.findBySalesOrderId(s.getSalesOrderId().getId(), false);
                    System.out.println("Sales order id is selected");
                    s.setSalesOrderId(salesOrderObj);

                    IFDBProduct dbProdObj = new DBProduct();
                    Product prodObj = dbProdObj.findProductByPartNo(s.getProductId().getPartNo(), false);
                    System.out.println("Part# is selected");
                    s.setProductId(prodObj);
                }
            }
        }
        catch (Exception e) {
            System.out.println("Query exception - select: "+e);
            e.printStackTrace();
        }
        return list;
    }
    private SalesLine singleWhere(String wClause, boolean retrieveAssociation){
        ResultSet results;
        SalesLine salesLineObj = new SalesLine();
        salesLineObj.setId(0);

        String query = buildQuery(wClause);
        System.out.println(query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if(results.next()){
                salesLineObj = buildSalesLine(results);
                stmt.close();
                if(retrieveAssociation){
                    IFDBSalesOrder dbSalesOrderObj = new DBSalesOrder();
                    SalesOrder salesOrderObj = dbSalesOrderObj.findBySalesOrderId(salesLineObj.getSalesOrderId().getId(), false);
                    System.out.println("Sales order id is selected");
                    salesLineObj.setSalesOrderId(salesOrderObj);

                    IFDBProduct dbProdObj = new DBProduct();
                    Product prodObj = dbProdObj.findProductByPartNo(salesLineObj.getProductId().getPartNo(), false);
                    System.out.println("Part# is selected");
                    salesLineObj.setProductId(prodObj);
                }
                else{
                    salesLineObj.setSalesOrderId(null);
                    salesLineObj.setProductId(null);
                }
            }
        }
        catch (Exception e) {
            System.out.println("Query exception: "+e);
        }
        return salesLineObj;
    }

    private String buildQuery(String wClause)
    {
        String query="SELECT * FROM SalesLine";
        if (wClause.length()>0){
            query=query+" WHERE "+ wClause;
        }
        return query;
    }

    private SalesLine buildSalesLine(ResultSet results){
        SalesLine sObj = new SalesLine();
        try {
            sObj.setId(results.getInt(1));
            sObj.setAmount(results.getInt(2));
            sObj.setPrice(results.getDouble(3));
            sObj.setTotalPrice(results.getDouble(4));
            sObj.setSalesOrderId(new SalesOrder(results.getInt(5)));
            sObj.setProductId(new Product(results.getInt(6)));
        }
        catch (Exception e) {
            System.out.println("error in building the product object");
        }
        return sObj;
    }
}
